﻿using System;
namespace HRM.Upgrade.Common
{
    public static class MySqlCommand
    {
        public static class Database
        {
            public const string DropAndCreate = @"IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Perfectmd')
                                                            begin
	                                                            alter database [Perfectmd] set single_user with rollback immediate;
	                                                            DROP DATABASE [Perfectmd];
	                                                            create database [Perfectmd];
                                                            end
                                                           else
	                                                            create database [Perfectmd];";
            public const string CreatePerfectmd = @"create database [Perfectmd];";
        }
        public static class Login
        {
            public const string DropAndCreate = @"IF  EXISTS (SELECT name FROM sys.syslogins WHERE name = N'infomd')
                                                begin
	                                                DROP LOGIN  [infomd]
	                                                CREATE LOGIN [infomd] WITH PASSWORD = '123456';
	                                                EXEC sp_addsrvrolemember 'infomd', 'sysadmin';
                                                end
                                                else
	                                                begin
		                                                CREATE LOGIN [infomd] WITH PASSWORD = '123456';
		                                                EXEC sp_addsrvrolemember 'infomd', 'sysadmin';
	                                                end";
            public const string CreateLogin = "CREATE LOGIN infomd WITH PASSWORD = '123456';";
            public const string AddRoleSysAdmin = "EXEC sp_addsrvrolemember 'infomd', 'sysadmin';";
        }

        public static class Tables
        {
            public const string ActiveLicense = @"use Perfectmd; CREATE TABLE [dbo].[ACTIVELICENSE](
	                                                [ID] [int] IDENTITY(1,1) NOT NULL,
	                                                [COMPUTERNAME] [nvarchar](50) NOT NULL,
	                                                [IPLAN] [varchar](50) NOT NULL,
	                                                [IPWAN] [varchar](50) NOT NULL,
	                                                [MAC] [varchar](50) NOT NULL,
	                                                [NAMESOFT] [nvarchar](255) NOT NULL,
	                                                [VERSION] [varchar](50) NOT NULL,
	                                                [NAMECOMPANY] [nvarchar](255) NOT NULL,
	                                                [DATETIME] [datetime] NOT NULL,
	                                                [ACTIONNAME] [nvarchar](255) NOT NULL,
	                                                [KEYCODE] [varchar](500) NULL,
	                                                [MACHINECODE] [varchar](500) NULL,
	                                                [ACTIVESTATUS] [nvarchar](255) NULL,
                                                    CONSTRAINT [PK_ACTIVELICENSE] PRIMARY KEY CLUSTERED 
                                                (
	                                                [ID] ASC
                                                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                                ) ON [PRIMARY];";

            public const string Customer = @"use Perfectmd; CREATE TABLE [dbo].[CUSTOMER](
	                                                [Id_Customer] [int] IDENTITY(1,1) NOT NULL,
	                                                [NameCompany] [nvarchar](255) NOT NULL,
	                                                [Address] [nvarchar](255) NULL,
	                                                [Tell] [varchar](50) NULL,
	                                                [Mobile] [nvarchar](50) NULL,
	                                                [Fax] [varchar](50) NULL,
	                                                [Website] [varchar](255) NULL,
	                                                [Email] [varchar](255) NULL,
	                                                [TaxID] [varchar](50) NULL,
	                                                [BusinessLicense] [varchar](50) NULL,
	                                                [ContactMan] [nvarchar](255) NULL,
	                                                [Contents] [ntext] NULL,
	                                                [Active] [bit] NULL,
	                                                [Notes] [ntext] NULL,
	                                                [ContactMan_A] [nvarchar](255) NULL,
	                                                [Position] [nvarchar](50) NULL,
	                                                [Typical] [bit] NULL,
	                                                [Collocation] [int] NULL,
                                                 CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED 
                                                (
	                                                [Id_Customer] ASC
                                                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                                ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];";

            public const string Contact = @"use Perfectmd; CREATE TABLE [dbo].[CONTACT](
	                                                [Id_Contact] [int] IDENTITY(1,1) NOT NULL,
	                                                [Id_TContact] [int] NULL,
	                                                [Name] [nvarchar](255) NOT NULL,
	                                                [NameCompany] [nvarchar](255) NOT NULL,
	                                                [Address] [nvarchar](255) NOT NULL,
	                                                [Tell] [varchar](50) NOT NULL,
	                                                [Mobile] [varchar](50) NULL,
	                                                [Fax] [varchar](50) NULL,
	                                                [Email] [varchar](255) NOT NULL,
	                                                [Website] [varchar](255) NULL,
	                                                [Id_HostField] [int] NULL,
	                                                [TaxID] [varchar](50) NULL,
	                                                [BusinessLicense] [varchar](50) NULL,
	                                                [Contents] [ntext] NOT NULL,
                                                 CONSTRAINT [PK_CONTACT] PRIMARY KEY CLUSTERED 
                                                (
	                                                [Id_Contact] ASC
                                                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                                ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];";
            public const string KeyCode = @"use Perfectmd; CREATE TABLE [dbo].[KEYCODE](
	                                                [Id_Key] [int] IDENTITY(1,1) NOT NULL,
	                                                [KeyCode] [varchar](500) NOT NULL,
	                                                [Id_Soft] [int] NULL,
	                                                [Id_Contact] [int] NULL,
	                                                [DateCreate] [datetime] NOT NULL,
	                                                [Property] [nvarchar](500) NULL,
	                                                [NumActive] [int] NOT NULL,
	                                                [Activated] [int] NOT NULL,
	                                                [Blacklist] [bit] NULL,
	                                                [TypeActive] [int] NULL,
	                                                [TypeSoft] [int] NULL,
	                                                [DateLimit] [datetime] NULL,
	                                                [Register] [bit] NULL,
	                                                [Technical_Staff] [int] NULL,
	                                                [Sales_Staff] [int] NULL,
	                                                [Money] [int] NULL,
	                                                [Payment] [int] NULL,
	                                                [DatePayment] [datetime] NULL,
	                                                [TypePayment] [nvarchar](50) NULL,
	                                                [Notes] [nvarchar](500) NULL,
	                                                [Id_Contract] [int] NULL,
	                                                [Number] [int] NULL,
                                                 CONSTRAINT [PK_KEYCODE] PRIMARY KEY CLUSTERED 
                                                (
	                                                [Id_Key] ASC
                                                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                                ) ON [PRIMARY];";
            public const string License = @"use Perfectmd; CREATE TABLE [dbo].[LICENSE](
	                                                [Id_License] [int] IDENTITY(1,1) NOT NULL,
	                                                [KeyCode] [varchar](500) NOT NULL,
	                                                [MachineCode] [varchar](500) NULL,
	                                                [LicenseCode] [varchar](500) NULL,
	                                                [Active] [bit] NULL,
	                                                [Version] [varchar](50) NULL,
	                                                [DateActive] [datetime] NULL,
	                                                [ReActive] [int] NULL,
                                                 CONSTRAINT [PK_LICENSE] PRIMARY KEY CLUSTERED 
                                                (
	                                                [Id_License] ASC
                                                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                                ) ON [PRIMARY];";
            public const string Soft = @"use Perfectmd; CREATE TABLE [dbo].[SOFT](
	                                                [Id_Soft] [int] IDENTITY(1,1) NOT NULL,
	                                                [Name] [nvarchar](255) NOT NULL,
	                                                [NewVersion] [varchar](50) NULL,
	                                                [Date] [datetime] NULL,
	                                                [VerHistory] [nvarchar](500) NULL,
	                                                [Pos] [int] NULL,
                                                 CONSTRAINT [PK_SOFT] PRIMARY KEY CLUSTERED 
                                                (
	                                                [Id_Soft] ASC
                                                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                                ) ON [PRIMARY];";
            public const string SoftStyle = @"use Perfectmd; CREATE TABLE [dbo].[SOFTSTYLE](
	                                            [Id_SoftStyle] [int] IDENTITY(1,1) NOT NULL,
	                                            [NameStyle] [nvarchar](200) NULL
                                            ) ON [PRIMARY];";
        }

        public static class StoredProcedure
        {
            public const string Customer_GetById = @"CREATE procedure [dbo].[CUSTOMER_GetById]
	                                                    (@Id_Customer int)
                                                    as
                                                    begin
	                                                    select * from CUSTOMER where Id_Customer=@Id_Customer
                                                    end";

            public const string Customer_Insert = @"CREATE PROCEDURE [dbo].[CUSTOMER_Insert]
	                                                --@Id_Customer INT,
	                                                @NameCompany NVARCHAR(255),
	                                                @Address NVARCHAR(255),
	                                                @Tell VARCHAR(50),
	                                                @Mobile NVARCHAR(50),
	                                                @Fax VARCHAR(50),
	                                                @Website VARCHAR(255),
	                                                @Email VARCHAR(255),
	                                                @TaxID VARCHAR(50),
	                                                @BusinessLicense VARCHAR(50),
	                                                @ContactMan NVARCHAR(255),
	                                                @Contents NTEXT,
	                                                @Active BIT,
	                                                @Notes NTEXT,
	                                                @ContactMan_A NVARCHAR(255),
	                                                @Position	nvarchar(50),
	                                                @Typical	bit,
	                                                @Collocation	int
                                                AS
                                                BEGIN
                                                    INSERT INTO CUSTOMER
                                                    VALUES
                                                      (
                                                        --@Id_Customer,
                                                        @NameCompany,
                                                        @Address,
                                                        @Tell,
                                                        @Mobile,
                                                        @Fax,
                                                        @Website,
                                                        @Email,
                                                        @TaxID,
                                                        @BusinessLicense,
                                                        @ContactMan,
                                                        @Contents,
                                                        @Active,
                                                        @Notes,
                                                        @ContactMan_A,@Position,
		                                                @Typical,@Collocation
                                                      )
                                                END";
            public const string Keycode_GetByKeyCode = @"CREATE PROCEDURE [dbo].[KEYCODE_GetByKeyCode]
	                                                    @KeyCode	varchar(500)
                                                     AS
                                                    begin
                                                    select * from KEYCODE 
                                                    where KeyCode=@KeyCode
                                                    end";
            public const string Keycode_Update = @"CREATE PROCEDURE [dbo].[KEYCODE_Update]
	                                                    @Id_Key		int,
	                                                    @KeyCode	varchar(500),
	                                                    @Id_Soft	int,
	                                                    @Id_Contact	int,
	                                                    @DateCreate	datetime,
	                                                    @Property	nvarchar(500),
	                                                    @NumActive	int,
	                                                    @Activated  int,
	                                                    @Blacklist	bit,
	                                                    @TypeActive		int,
	                                                    @TypeSoft	int,
	                                                    @DateLimit	datetime,
	                                                    @Register	bit,
	                                                    @Technical_Staff int,
	                                                    @Sales_Staff int,
	                                                    @Money int,@Number	int
                                                     AS
                                                    begin
                                                    update KEYCODE 
                                                    set KeyCode=@KeyCode,Id_Soft=@Id_Soft,Id_Contact=@Id_Contact,DateCreate=@DateCreate,Property=@Property,
	                                                    NumActive=@NumActive,Activated=@Activated,Blacklist=@Blacklist,TypeActive=@TypeActive,TypeSoft=@TypeSoft,
	                                                    DateLimit=@DateLimit,Register=@Register,Technical_Staff=@Technical_Staff,Sales_Staff=@Sales_Staff,
	                                                    Money=@Money,Number=@Number
                                                    where Id_Key=@Id_Key
                                                    end";
            public const string Keycode_Insert = @"CREATE PROCEDURE [dbo].[KEYCODE_Insert]
	                                                    @KeyCode	varchar(500),
	                                                    @Id_Soft	int,
	                                                    @Id_Contact	int,
	                                                    @DateCreate	datetime,
                                                        @Property nvarchar(500),
	                                                    @NumActive	int,
	                                                    @Activated  int,
	                                                    @Blacklist	bit,
	                                                    @TypeActive		int,
	                                                    @TypeSoft	int,
	                                                    @DateLimit	datetime,
	                                                    @Register	bit,
	                                                    @Technical_Staff int,
	                                                    @Sales_Staff int,
	                                                    @Money int,
	                                                    @Payment int
                                                        ,@DatePayment datetime
                                                        ,@TypePayment nvarchar(50)
                                                        ,@Notes nvarchar(500)
	                                                    ,@Id_Contract int
	                                                    ,@Number	int
                                                        AS
                                                    begin
                                                    INSERT INTO [dbo].[KEYCODE]
                                                        ([KeyCode]
                                                        ,[Id_Soft]
                                                        ,[Id_Contact]
                                                        ,[DateCreate]
                                                        ,[Property]
                                                        ,[NumActive]
                                                        ,[Activated]
                                                        ,[Blacklist]
                                                        ,[TypeActive]
                                                        ,[TypeSoft]
                                                        ,[DateLimit]
                                                        ,[Register]
                                                        ,[Technical_Staff]
                                                        ,[Sales_Staff]
                                                        ,[Money]
                                                        ,[Payment]
                                                        ,[DatePayment]
                                                        ,[TypePayment]
                                                        ,[Notes]
                                                        ,[Id_Contract]
                                                        ,[Number])
                                                    VALUES
                                                        (@KeyCode
                                                        ,@Id_Soft
                                                        ,@Id_Contact
                                                        ,@DateCreate
                                                        ,@Property
                                                        ,@NumActive
                                                        ,@Activated
                                                        ,@Blacklist
                                                        ,@TypeActive
                                                        ,@TypeSoft
                                                        ,@DateLimit
                                                        ,@Register
                                                        ,@Technical_Staff
                                                        ,@Sales_Staff
                                                        ,@Money
                                                        ,@Payment
                                                        ,@DatePayment
                                                        ,@TypePayment
                                                        ,@Notes
                                                        ,@Id_Contract
                                                        ,@Number)
                                                    end";

            public const string License_GetByKeyCode = @"CREATE PROCEDURE [dbo].[LICENSE_GetByKeyCode]
	                                                    @KeyCode	varchar(500)
                                                     AS
                                                    begin
                                                    select * from LICENSE 
                                                    where KeyCode=@KeyCode
                                                    end";
            public const string License_GetMachKeyCode = @"CREATE PROCEDURE [dbo].[LICENSE_GetMachKeyCode]
	                                                    @KeyCode	varchar(500),
	                                                    @MachineCode	varchar(500)
                                                     AS
                                                    begin
                                                    select * from LICENSE 
                                                    where KeyCode=@KeyCode and MachineCode=@MachineCode
                                                    end";
            public const string License_Insert = @"CREATE PROCEDURE [dbo].[LICENSE_Insert]
	                                                    @KeyCode	varchar(500),
	                                                    @MachineCode	varchar(500),
	                                                    @Active	bit,
	                                                    @Version	varchar(50)
                                                     AS
                                                    begin
                                                    insert into LICENSE 
                                                    values(@KeyCode,@MachineCode,'',@Active,@Version,getDate(),0)
                                                    exec KEYCODE_IncreaseActivated @KeyCode,1
                                                    end";
            public const string License_Update = @"CREATE PROCEDURE [dbo].[LICENSE_Update]
	                                                    @Id_License	int,
	                                                    @KeyCode	varchar(500),
	                                                    @MachineCode	varchar(500),
	                                                    @LicenseCode	varchar(500),
	                                                    @Active	bit,
	                                                    @Version	varchar(50)
                                                        AS
                                                    begin
                                                    update LICENSE 
                                                    set KeyCode=@KeyCode,
	                                                    MachineCode=@MachineCode,LicenseCode=@LicenseCode,
	                                                    Active=@Active,Version=@Version
                                                    where Id_License=@Id_License
                                                    end";
            public const string License_UpdateReActive = @"CREATE PROCEDURE [dbo].[LICENSE_UpdateReActive]
	                                                    @KeyCode	varchar(500),
	                                                    @MachineCode	varchar(500)	
                                                    AS
                                                    begin
                                                    update LICENSE 
                                                    set 
	                                                    ReActive=ReActive+1,
	                                                    DateActive=Getdate()
                                                    where KeyCode=@KeyCode and MachineCode=@MachineCode

                                                    if (@@Error>0 or @@rowcount=0)
	                                                    Begin
		                                                    rollback tran
		                                                    raiserror('Không thực hiện được.',16,1)
		                                                    return
	                                                    END
	
                                                    end";
            public const string License_InsertName = @"CREATE PROCEDURE [dbo].[LICENSE_InsertName]
	                                                    @NameSoft	nvarchar(200),
	                                                    @KeyCode	varchar(500),
	                                                    @MachineCode	varchar(500),
	                                                    @Active	bit,
	                                                    @Version	varchar(50)
                                                        AS
                                                    begin tran

                                                    declare @Id_Soft int
                                                    select 
	                                                    @Id_Soft=Id_Soft
                                                    From
	                                                    SOFT
                                                    Where
	                                                    [Name]=@NameSoft	

                                                    declare @SoftOfKeyCode int

                                                    select 
	                                                    @SoftOfKeyCode=Id_Soft
                                                    from
	                                                    KEYCODE
                                                    where 
	                                                    KeyCode= @KeyCode

                                                    if (@SoftOfKeyCode<>@Id_Soft)
	                                                    Begin

		                                                    rollback tran
		
		                                                    return
	                                                    End


                                                    declare @DateActive datetime
                                                    set @DateActive=getdate()
                                                    insert into LICENSE 
                                                    values(@KeyCode,
	                                                    @MachineCode,'',@Active,@Version,@DateActive,0)

                                                    if (@@Error>0 or @@rowcount=0)
	                                                    Begin
		                                                    rollback tran
		                                                    return
	                                                    End

                                                    update KEYCODE set Activated=Activated+1,Register=1 where KeyCode=@KeyCode AND Activated<NumActive
                                                    if (@@Error>0 or @@rowcount=0)
	                                                    Begin
		                                                    rollback tran
		                                                    return
	                                                    End
                                                    commit";
            //public const string License_Update = @"";
            //public const string License_Update = @"";
            //public const string License_Update = @"";
        }
    }
}
